package mes.kanban.respository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import mes.kanban.entity.MesMaterial;

/**
 * 素材管理
 * @author zongrui.liu
 */
public interface MaterialRespository extends BaseJpaRespository<MesMaterial, String> {

	@Query("from MesMaterial where id in ?1")
	List<MesMaterial> findMateriasById(List<String> ids);

	
	@Query(nativeQuery=true,value="SELECT DISTINCT m.* FROM mes_material m where m.creator = :userId order by m.create_time desc limit :stratCount,:pageSize ")
	List<MesMaterial> findMaterialListByPage(@Param("stratCount")int stratCount, @Param("pageSize")int pageSize, @Param("userId")String userId);
	
	@Query(nativeQuery=true,value="SELECT DISTINCT m.* FROM mes_material m LEFT JOIN mes_user u ON u.id = m.creator LEFT JOIN mes_user_role ur ON ur.user_id = u.id LEFT JOIN mes_role r ON r.id = ur.role_id INNER JOIN ( SELECT r.id FROM mes_user u LEFT JOIN mes_user_role ur ON u.id = ur.user_id LEFT JOIN mes_role r ON ur.role_id = r.id WHERE u.id = :userId ) ur1 ON ur1.id = r.id :ConditionStr order by m.create_time desc  ")
	List<MesMaterial> findMaterialListByUserIdAndCondition(@Param("userId")String userId,@Param("ConditionStr")String ConditionStr);
	
	@Query(nativeQuery=true,value="SELECT DISTINCT m.* FROM mes_material m LEFT JOIN mes_user u ON u.id = m.creator LEFT JOIN mes_user_role ur ON ur.user_id = u.id LEFT JOIN mes_role r ON r.id = ur.role_id INNER JOIN ( SELECT r.id FROM mes_user u LEFT JOIN mes_user_role ur ON u.id = ur.user_id LEFT JOIN mes_role r ON ur.role_id = r.id WHERE u.id = :userId ) ur1 ON ur1.id = r.id :ConditionStr order by m.create_time desc limit :stratCount,:pageSize ")
	List<MesMaterial> findMaterialListByPageAndCondition(@Param("stratCount")int stratCount, @Param("pageSize")int pageSize, @Param("userId")String userId,@Param("ConditionStr")String ConditionStr);

	List<MesMaterial> findMaterialByCreator(String userId);
	

}
